package com.lemon.components.ant.util;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLLimit;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLInSubQueryExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.expr.SQLVariantRefExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.ast.statement.SQLJoinTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.ast.statement.SQLUnionQuery;
import com.alibaba.druid.util.JdbcConstants;
import com.lemon.components.ant.project.beans.ColumnInfo;
import com.lemon.components.ant.project.beans.MethodInfo;
import com.lemon.components.ant.project.beans.SqlParserInfo;
import com.lemon.components.ant.project.beans.TableInfo;
import com.lemon.components.docs.mysql.MysqlLocalTool;
import com.lemon.ui.codemaker.component.RecentlyFile;
import com.system.core.security.NameUtil;

public class DruidSQLParser {

	public static void main(String[] args) {
		String sql = "SELECT\r\n" + 
				"	boss_user.user_name,\r\n" + 
				"	sys_role.role_name,\r\n" + 
				"	boss_user.is_locked,\r\n" + 
				"	boss_user.user_role_id\r\n" + 
				"FROM\r\n" + 
				"	boss_user\r\n" + 
				"INNER JOIN sys_role ON boss_user.user_role_id = sys_role.id\r\n" + 
				"WHERE\r\n" + 
				"	boss_user.nick = 'admin'\r\n" + 
				"AND boss_user.phone = '15923325427'\r\n" + 
				"OR boss_user.create_time > ?\r\n" + 
				"AND boss_user.password < 'operator'\r\n" + 
				"AND boss_user.create_time < CURRENT_TIME\r\n" + 
				"OR sys_role.role_code = ?";

		DruidSQLParser druidSQLParser = new DruidSQLParser();
		SqlParserInfo sqlParserInfo = druidSQLParser.parseSql(sql);
	}

	public SqlParserInfo parseSql(String sql) {
		List<TableInfo> tables = MysqlLocalTool.getTableInfo(ConfigUtil.getInstance(RecentlyFile.configFilePath).getProjectInfo());
		MethodInfo methodInfo = ConfigUtil.getInstance(RecentlyFile.configFilePath).getMethodInfo();
		SqlParserInfo sqlParserInfo = new SqlParserInfo();
		StringBuilder mybatisXml = new StringBuilder();
		String modelBasePackage = methodInfo.getServiceInfo().getProjectInfo().getGroupId()+".dal.mybatis.mymodel.";
		String rspModel = modelBasePackage + StringUtils.capitalize(methodInfo.getMethodName())+"RspDto";
		String reqModel = modelBasePackage + StringUtils.capitalize(methodInfo.getMethodName())+"ReqDto";
		mybatisXml.append("<select id=\""+methodInfo.getMethodName()+"\" resultType=\""+ rspModel +"\" parameterType=\""+reqModel+"\">");
        // 解析
        List<SQLStatement> statements = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
        // 只考虑一条语句
        SQLStatement statement = statements.get(0);
        // 只考虑查询语句
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;
        SQLSelectQuery     sqlSelectQuery     = sqlSelectStatement.getSelect().getQuery();
        // 非union的查询语句
        if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
            xml(mybatisXml,sqlSelectQueryBlock,tables, sqlParserInfo);
        // union的查询语句
        } else if (sqlSelectQuery instanceof SQLUnionQuery) {
        	SQLUnionQuery sqlUnionQuery = (SQLUnionQuery) sqlSelectQuery;
        	xml(mybatisXml,(SQLSelectQueryBlock)sqlUnionQuery.getLeft(),tables, sqlParserInfo);
        	mybatisXml.append("\n\tUNION\n\t");
        	xml(mybatisXml,(SQLSelectQueryBlock)sqlUnionQuery.getRight(),tables, sqlParserInfo);
        }
        mybatisXml.append("\n</select>");
        sqlParserInfo.setSqlMap(mybatisXml.toString());
        System.out.println(mybatisXml.toString());
        return sqlParserInfo;
    }
	
	private void xml(StringBuilder sb,SQLSelectQueryBlock sqlSelectQueryBlock,List<TableInfo> tables, SqlParserInfo sqlParserInfo) {
		List<ColumnInfo> columns = new ArrayList<ColumnInfo>();
		sb.append("\n\tSELECT");
		// 获取字段列表
        List<SQLSelectItem> selectItems = sqlSelectQueryBlock.getSelectList();
        for(int i=0;i<selectItems.size();i++) {
        	sb.append("\n\t\t"+selectItems.get(i).toString());
        	if(i < selectItems.size()-1)
        	sb.append(",");
        	if(selectItems.get(i).getExpr() instanceof SQLPropertyExpr) {
        		SQLPropertyExpr item = (SQLPropertyExpr)selectItems.get(i).getExpr();
        		ColumnInfo columnInfo = MysqlLocalTool.getColumnInfo(tables, item.getOwnernName(), item.getName());
        		if(columnInfo != null) {
        			columns.add(columnInfo);
        		} else {
        			System.err.println(item.getOwnernName()+"表的"+item.getName()+"属性未找到");
        		}
        	} else if(selectItems.get(i).getExpr() instanceof SQLAggregateExpr) {
        		SQLAggregateExpr item = (SQLAggregateExpr)selectItems.get(i).getExpr();
        	}
        }
        sqlParserInfo.setRsp(columns);
        // 获取表
        SQLTableSource table = sqlSelectQueryBlock.getFrom();
        sb.append("\n\tFROM ");
        // 普通单表
        if (table instanceof SQLExprTableSource) {
        	sb.append(table.toString());
        // join多表
        } else if (table instanceof SQLJoinTableSource) {
        	sb.append(table.toString());
        // 子查询作为表
        } else if (table instanceof SQLSubqueryTableSource) {
        	sb.append(table.toString());
        }
        // 获取where条件
        SQLExpr where = sqlSelectQueryBlock.getWhere();
        sb.append("\n\t<where>");
        // 如果是二元表达式
        if (where instanceof SQLBinaryOpExpr) {
            SQLBinaryOpExpr   sqlBinaryOpExpr = (SQLBinaryOpExpr) where;
            LinkedList<String> operators = new LinkedList<String>();
            LinkedList<SQLBinaryOpExpr> wheres = new LinkedList<SQLBinaryOpExpr>();
            where(wheres,sqlBinaryOpExpr);

            sqlParserInfo.setReq(getRequestColumns(wheres));
            
            for(int i=0; i < wheres.size(); i++) {
            	if(wheres.get(i).getLeft() instanceof SQLPropertyExpr) {
            		SQLPropertyExpr item = (SQLPropertyExpr)wheres.get(i).getLeft();
            		ColumnInfo columnInfo = MysqlLocalTool.getColumnInfo(tables, item.getOwnernName(), item.getName());
            		//if条件判断
            		if(columnInfo != null) {
            			if(columnInfo.getJavaType().equals("String")) {
            				sb.append("\n\t\t<if test=\""+item.getName()+" != null and "+item.getName()+" != '' \">");
            			} else {
            				sb.append("\n\t\t<if test=\""+item.getName()+" != null \">");
            			}
            		}
            		sb.append("\n\t\t<![CDATA[");
            		//操作符
            		sb.append("\n\t\t\t");
            		//查询属性
            		StringBuilder opt = new StringBuilder();
            		if(wheres.get(i).getRight() instanceof SQLVariantRefExpr) {
            			getOperator(wheres.get(i),sqlBinaryOpExpr,opt);
            			sb.append(opt+" ");
            			sb.append(item.getOwner()+"."+item.getName() + " " +wheres.get(i).getOperator().nameLCase+" ${"+ NameUtil._2JavaName(item.getName()) +"}");
            		} else {
            			getOperator(wheres.get(i),sqlBinaryOpExpr,opt);
            			sb.append(opt+" ");
            			sb.append(item.getOwner()+"."+item.getName() +" "+ wheres.get(i).getOperator().nameLCase +" "+ wheres.get(i).getRight().toString());
            		}
            		sb.append("\n\t\t]]>");
            		sb.append("\n\t\t</if>");
            	} else if(wheres.get(i).getLeft() instanceof SQLIdentifierExpr){
            		SQLIdentifierExpr item = (SQLIdentifierExpr)wheres.get(i).getLeft();
            		sb.append("\n\t\t<if test=\""+item.getName()+" != null and "+item.getName()+" != '' \">");
            		sb.append("\n\t\t<![CDATA[");
            		if(i > 0) {
            			sb.append("\n\t\t\t" + operators.get(i-1).toUpperCase()+" ");
            		} else {
            			sb.append("\n\t\t\t");
            		}
            		if(wheres.get(i).getRight() instanceof SQLVariantRefExpr) {
            			sb.append(item.getName()+"=${"+NameUtil._2JavaName(item.getName())+"}");
            		} else {
            			sb.append(item.getName()+"="+wheres.get(i).getRight());
            		}
            		sb.append("\n\t\t]]>");
            		sb.append("\n\t\t</if>");
            	}
            }
            
//            SQLBinaryOpExpr           left            = (SQLBinaryOpExpr)sqlBinaryOpExpr.getLeft();
//            SQLBinaryOperator operator        = sqlBinaryOpExpr.getOperator();
//            SQLExpr           right           = sqlBinaryOpExpr.getRight();
            // 处理---------------------
        // 如果是子查询
        } else if (where instanceof SQLInSubQueryExpr) {
            SQLInSubQueryExpr sqlInSubQueryExpr = (SQLInSubQueryExpr) where;
            // 处理---------------------
        }
        sb.append("\n\t</where>");
        // 获取分组
        SQLSelectGroupByClause groupBy = sqlSelectQueryBlock.getGroupBy();
        if(groupBy != null) {
        	List<SQLExpr> items = groupBy.getItems();
        	if(items.size() > 0) {
        		sb.append("\n\tGROUP BY");
        		for(int i=0; i < items.size(); i++) {
        			sb.append("\n\t\t"+items.get(i));
        			if(i < items.size()-1)
        				sb.append(",");
        		}
        	}
        }
        // 处理---------------------
        // 获取排序
        SQLOrderBy orderBy = sqlSelectQueryBlock.getOrderBy();
        if(orderBy != null) {
        	List<SQLSelectOrderByItem> orderByItems = orderBy.getItems();
        	if(orderByItems != null) {
        		if(orderByItems.size() > 0) {
        			sb.append("\n\tORDER BY");
        			for(int i=0; i < orderByItems.size(); i++) {
        				sb.append("\n\t\t"+orderByItems.get(i).getExpr()+" "+(orderByItems.get(i).getType()==null?"":orderByItems.get(i).getType()));
        				if(i < orderByItems.size()-1)
        					sb.append(",");
        			}
        		}
        	}
        }
        // 处理---------------------
        // 获取分页
        SQLLimit limit = sqlSelectQueryBlock.getLimit();
        if(limit != null)
        	sb.append("\n\tLIMIT "+limit.getOffset()+","+limit.getRowCount());
	}
	
	public void where(LinkedList<SQLBinaryOpExpr> wheres,SQLBinaryOpExpr sqlBinaryOpExpr) {
		if(sqlBinaryOpExpr.getLeft() instanceof SQLBinaryOpExpr) {
			where(wheres,(SQLBinaryOpExpr)sqlBinaryOpExpr.getLeft());
		} else {
			wheres.add(sqlBinaryOpExpr);
		}
		
		if(sqlBinaryOpExpr.getRight() instanceof SQLBinaryOpExpr) {
			where(wheres,(SQLBinaryOpExpr)sqlBinaryOpExpr.getRight());
		}
	}
	
	public static void getOperator(SQLBinaryOpExpr item,SQLBinaryOpExpr sqlBinaryOpExpr,StringBuilder opt) {
		if(sqlBinaryOpExpr.getLeft() instanceof SQLBinaryOpExpr) {
			getOperator(item,(SQLBinaryOpExpr)sqlBinaryOpExpr.getLeft(),opt);
		}
		
		if(sqlBinaryOpExpr.getRight() instanceof SQLBinaryOpExpr) {
			if(sqlBinaryOpExpr.getRight().toString().toLowerCase().startsWith(item.toString().toLowerCase())) {
//				System.out.println("找到："+item.toString());
				opt.append(sqlBinaryOpExpr.getOperator().nameLCase.toUpperCase());
			}
			getOperator(item,(SQLBinaryOpExpr)sqlBinaryOpExpr.getRight(),opt);
		}
	}
	
	public List<ColumnInfo> getRequestColumns(LinkedList<SQLBinaryOpExpr> where) {
		List<ColumnInfo> req = new ArrayList<ColumnInfo>();
		List<TableInfo> tables = MysqlLocalTool.getTableInfo(ConfigUtil.getInstance(RecentlyFile.configFilePath).getProjectInfo());
		for(SQLBinaryOpExpr item : where) {
			if(item.getRight() instanceof SQLVariantRefExpr) {
				SQLPropertyExpr property = (SQLPropertyExpr)item.getLeft();
				ColumnInfo columnInfo = MysqlLocalTool.getColumnInfo(tables, property.getOwnernName(), property.getName());
				req.add(columnInfo);
			}
		}
		return req;
	}

}
